﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using JKF.DataModule.IDAL;
using JKF.Substructure;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace JKF.DataModule.SqlServerDAL
{
    public partial class dalCUSTOMER : IdalCUSTOMER
    {
        // [dbo].[getDic_AreaAllName] 自定义的函数，用于获取省市县
        private const string FieldShow = "*";
        private const string FieldOrder = "CARD_NO,[NAME]";
        private const string FieldKey = "CUSTOMER_KEY";

        /// <summary>
        /// 返回客户表某一页数据
        /// </summary>
        /// <param name="dateStart">起始有效期</param>
        /// <param name="dateEnd">截至有效期</param>
        /// <param name="field">查询字段</param>
        /// <param name="fieldOp">查询字段操作符</param>
        /// <param name="fieldKey">查村内容</param>
        /// <param name="pageCurrent">当前页</param>
        /// <param name="pageSize"></param>
        /// <param name="pageCount"></param>
        /// <param name="rowCount"></param>
        /// <returns></returns>
        public static DataTable GetCurstomerPage(string dateStart, string dateEnd, string field, string fieldOp, string fieldKey, string ownShop, string currentUser,
            int pageCurrent, int pageSize, ref int pageCount, ref int rowCount)
        {
            var sql = new StringBuilder();
            DateTime dtTmp;
            sql.AppendFormat(" shop_code in(select shop_code from sys_user_shop where user_key='{0}') and STATUS='1' ", currentUser);

            if (!string.IsNullOrEmpty(ownShop))
            {
                sql.AppendFormat(" and Shop_code in('{0}')", string.Join("','", ownShop.Split(',')));
            }

            if (!string.IsNullOrEmpty(field) && !string.IsNullOrEmpty(fieldOp) && !string.IsNullOrEmpty(fieldKey))
            {
                if (field.ToUpper() == "LEVEL_CODE")
                {
                    sql.AppendFormat((fieldOp == "=" ? " AND level_name='{0}'" : " AND level_name LIKE '%{0}%'"), fieldKey);
                }
                else
                {
                    sql.AppendFormat((fieldOp == "=" ? " AND {0}='{1}'" : " AND {0} LIKE '%{1}%'"), field, fieldKey);
                }
            }
            // 下面调用此方法去查询数据库
            return DataPage.GetPageInfo("VL_CUSTOMER_VIEW", FieldKey, pageCurrent, pageSize, FieldShow, FieldOrder, sql.ToString(), ref pageCount, ref rowCount).Tables[0];

        }

        /// <summary>
        /// 删除顾客
        /// </summary>
        /// <param name="customerKey"></param>
        public static void DeleteCustomer(Guid customerKey)
        {
            //proc_customer_delete
            //@CUSTOMER_KEY
            var command = DB.HR.GetStoredProcCommand("proc_customer_delete");
            DB.HR.AddInParameter(command, "@CUSTOMER_KEY", DbType.String, customerKey.ToString());
            DB.HR.ExecuteNonQuery(command);
        }

        /// <summary>
        /// 判断卡号是否存在
        /// </summary>
        /// <param name="cardNo">待检测卡号</param>
        /// <returns></returns>
        public bool CardNoExists(string cardNo, string customerKey)
        {
            Database db = DB.HR;
            var strSql = new StringBuilder();
            strSql.Append("SELECT DISTINCT CUSTOMER_KEY FROM (SELECT CUSTOMER_KEY FROM CUSTOMER WHERE CARD_NO=@CARDNO UNION SELECT CUSTOMER_KEY FROM CUSTOMER_CARD_CHANGE WHERE OLD_CARD=@CARDNO UNION SELECT CUSTOMER_KEY FROM RECYCLE_CUSTOMER WHERE CARD_NO=@CARDNO) AS CARD_NO_COUNT " + (customerKey.IsGuidB() ? string.Format(" WHERE CUSTOMER_KEY<>'{0}'", customerKey) : ""));
            var dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "CARDNO", DbType.String, cardNo);
            int cmdresult;
            var dts = db.ExecuteDataSet(dbCommand);
            return (dts != null && dts.Tables.Count > 0 && dts.Tables[0].Rows.Count > 0);
        }

        /// <summary>
        /// 判断卡号是否存在
        /// </summary>
        /// <param name="mobile">待检测手机号</param>
        /// <returns></returns>
        public bool MobileExists(string mobile, string customerKey)
        {
            Database db = DB.HR;
            var strSql = new StringBuilder();
            strSql.Append("select count(1) from CUSTOMER where mobile=@mobile " + (customerKey.IsGuidB() ? string.Format(" and CUSTOMER_KEY<>'{0}'", customerKey) : ""));
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "mobile", DbType.String, mobile);
            int cmdresult;
            var obj = db.ExecuteScalar(dbCommand);
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            return cmdresult != 0;
        }

        /// <summary>
        /// 通过手机号获得人员Key
        /// </summary>
        /// <returns></returns>
        public static string GetCustomerKeyByMobileNo(string mobileNo)
        {
            var dt =
                DB.HR.ExecuteDataSet(CommandType.Text,
                                     string.Format("SELECT top 1 CUSTOMER_KEY FROM CUSTOMER WHERE MOBILE='{0}'",
                                                   mobileNo)).Tables[0];
            return dt.Rows.Count > 0 ? dt.Rows[0][0].ToString() : string.Empty;
        }

        /// <summary>
        /// 高级查询顾客page
        /// </summary>
        /// <param name="cardNo"></param>
        /// <param name="levelCode">逗号分割levelcode</param>
        /// <param name="name"></param>
        /// <param name="sex"></param>
        /// <param name="refereeCard"></param>
        /// <param name="referee"></param>
        /// <param name="idCard"></param>
        /// <param name="ageFrom"></param>
        /// <param name="ageTo"></param>
        /// <param name="mobile"></param>
        /// <param name="phone"></param>
        /// <param name="groupKey">逗号分割groupKey</param>
        /// <param name="areaCode"></param>
        /// <param name="birthdayFrom"></param>
        /// <param name="birthdayTo"></param>
        /// <param name="dateCreateStart"></param>
        /// <param name="dateCreateEnd"></param>
        /// <param name="storyMoneyFrom"></param>
        /// <param name="storyMoneyTo"></param>
        /// <param name="avaPointFrom"></param>
        /// <param name="avaPointTo"></param>
        /// <param name="currentUser"></param>
        /// <param name="pageCurrent"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageCount"></param>
        /// <param name="rowCount"></param>
        /// <returns></returns>
        public static DataTable GetCustomerAdvPage(string cardNo, string levelCode, string name,
            string sex, string refereeCard, string referee,
            string idCard, int? ageFrom, int? ageTo,
            string mobile, string phone, string groupKey,
            string areaCode, DateTime? birthdayFrom, DateTime? birthdayTo,
            DateTime? dateCreateStart, DateTime? dateCreateEnd, int? avaPointFrom, int? avaPointTo, string currentUser,
            string title, string family,
            int pageCurrent, int pageSize, ref int pageCount, ref int rowCount)
        {
            var sql = new StringBuilder();
            sql.AppendFormat(" shop_code in(select shop_code from sys_user_shop where user_key='{0}') and STATUS='1' ", currentUser);

            #region condition
            if (!string.IsNullOrEmpty(cardNo)) sql.AppendFormat(" AND CARD_NO='{0}'", cardNo);
            if (!string.IsNullOrEmpty(levelCode))
            {
                var codes = levelCode.Split(',');
                if (codes.Length > 1)
                {
                    sql.AppendFormat(" AND LEVEL_CODE in('{0}')", string.Join("','", codes));
                }
                else
                {
                    sql.AppendFormat(" AND LEVEL_CODE='{0}'", codes[0]);
                }
            }
            if (!string.IsNullOrEmpty(name)) sql.AppendFormat(" AND [NAME]='{0}'", name);
            if (!string.IsNullOrEmpty(sex)) sql.AppendFormat(" AND SEX='{0}'", sex);
            if (!string.IsNullOrEmpty(refereeCard)) sql.AppendFormat(" AND REFEREE_CARD='{0}'", refereeCard);
            if (!string.IsNullOrEmpty(referee)) sql.AppendFormat(" AND REFEREE='{0}'", referee);
            if (!string.IsNullOrEmpty(idCard)) sql.AppendFormat(" AND ID_CARD='{0}'", idCard);
            if (!string.IsNullOrEmpty(mobile)) sql.AppendFormat(" AND MOBILE='{0}'", mobile);
            if (!string.IsNullOrEmpty(phone)) sql.AppendFormat(" AND phone='{0}'", phone);

            if (!string.IsNullOrEmpty(title)) sql.AppendFormat(" AND title like '%{0}%'", title);
            if (!string.IsNullOrEmpty(family)) sql.AppendFormat(" AND family like '%{0}%'", family);

            if (!string.IsNullOrEmpty(groupKey))
            {
                var keys = groupKey.Split(',');
                var avaKeys = (from string s in keys where s.IsGuidB() select s).ToList();
                if (avaKeys.Count > 1)
                {
                    sql.AppendFormat(" AND GROUP_KEY in('{0}')", string.Join("','", avaKeys));
                }
                else
                {
                    sql.AppendFormat(" AND GROUP_KEY='{0}'", avaKeys[0]);
                }
            }
            if (!string.IsNullOrEmpty(areaCode)) sql.AppendFormat(" AND AREA_CODE like'{0}%'", areaCode);

            if (ageFrom.HasValue) sql.AppendFormat(" AND (datepart(yyyy,getdate())-datepart(yyyy,ISNULL(BIRTHDAY,dateadd(yyyy,1,GETDATE())))+1)>={0}", ageFrom.Value);
            if (ageTo.HasValue) sql.AppendFormat(" AND (datepart(yyyy,getdate())-datepart(yyyy,ISNULL(BIRTHDAY,dateadd(yyyy,1,GETDATE())))+1)<={0}", ageTo.Value);
            #endregion

            if (birthdayFrom.HasValue)
            {
                sql.AppendFormat(" and birthday>='{0}'", birthdayFrom.ToShortDateLineFormat());
            }
            if (birthdayTo.HasValue)
            {
                sql.AppendFormat(" and birthday<='{0}'", birthdayFrom.ToShortDateLineFormat() + " 23:59:59");
            }

            if (dateCreateStart.HasValue) sql.AppendFormat(" and DATE_CREATE>= convert(datetime,'{0}')", dateCreateStart.Value);
            if (dateCreateEnd.HasValue) sql.AppendFormat(" and DATE_CREATE<= convert(datetime,'{0}')", dateCreateEnd.Value.ToString("yyyy-MM-dd") + " 23:59:59");
            if (avaPointFrom.HasValue) sql.AppendFormat(" and (GIVE_POINTS+CONSUME_POINTS)>={0}", avaPointFrom.Value);
            if (avaPointTo.HasValue) sql.AppendFormat(" and (GIVE_POINTS+CONSUME_POINTS)<={0}", avaPointTo.Value);
            return DataPage.GetPageInfo("VL_CUSTOMER_VIEW", FieldKey, pageCurrent, pageSize, FieldShow, FieldOrder, sql.ToString(), ref pageCount, ref rowCount).Tables[0];
        }

        /// <summary>
        /// 查询拥有手机号的人员
        /// </summary>
        /// <param name="customerCardNo"></param>
        /// <param name="customerName"></param>
        /// <param name="dateStart"></param>
        /// <param name="dateEnd"></param>
        /// <param name="customerLevel"></param>
        /// <param name="gender"></param>
        /// <param name="ageFrom"></param>
        /// <param name="ageTo"></param>
        /// <param name="pointFrom"></param>
        /// <param name="pointTo"></param>
        /// <param name="areaCode"></param>
        /// <param name="shopName"></param>
        /// <param name="currentUser"></param>
        /// <param name="noMobile"></param>
        /// <returns></returns>
        public static DataTable GetCustomer4MobileSelect(string customerCardNo, string customerName, DateTime? dateStart,
            DateTime? dateEnd, string customerLevel, string gender, int? ageFrom, int? ageTo, int? pointFrom, int? pointTo,
            string areaCode, string shopName, string currentUser, bool noMobile, string mobileNum, string shopCode)
        {
            var sqlBuilder = new StringBuilder("SELECT CUSTOMER_KEY,CARD_NO,[NAME],MOBILE FROM CUSTOMER WHERE ");
            sqlBuilder.AppendFormat("shop_code in(select shop_code from sys_user_shop where user_key='{0}')", currentUser);
            if (!string.IsNullOrEmpty(customerCardNo)) sqlBuilder.AppendFormat(" AND CARD_NO='{0}'", customerCardNo);
            if (!string.IsNullOrEmpty(customerName)) sqlBuilder.AppendFormat(" AND [NAME] LIKE '%{0}%'", customerName);
            if (dateStart.HasValue) sqlBuilder.AppendFormat(" AND (BIRTHDAY IS NOT NULL AND BIRTHDAY>='{0}')", dateStart.Value.ToShortDateLineFormat());
            if (dateEnd.HasValue) sqlBuilder.AppendFormat(" AND (BIRTHDAY IS NOT NULL AND BIRTHDAY<='{0}')", dateEnd.Value.ToShortDateLineFormat());
            if (!string.IsNullOrEmpty(customerLevel)) sqlBuilder.AppendFormat(" AND LEVEL_CODE='{0}'", customerLevel);
            if (!string.IsNullOrEmpty(gender)) sqlBuilder.AppendFormat(" AND SEX='{0}'", gender);
            if (ageFrom.HasValue) sqlBuilder.AppendFormat(" AND (birthday is not null and (datepart(yyyy,getdate())-datepart(yyyy,birthday)+1)>={0})", ageFrom);
            if (ageTo.HasValue) sqlBuilder.AppendFormat(" AND (birthday is not null and (datepart(yyyy,getdate())-datepart(yyyy,birthday)+1)<={0})", ageTo);
            if (pointFrom.HasValue) sqlBuilder.AppendFormat(" AND (GIVE_POINTS+CONSUME_POINTS)>={0}", pointFrom);
            if (pointTo.HasValue) sqlBuilder.AppendFormat(" AND (GIVE_POINTS+CONSUME_POINTS)<={0}", pointTo);
            if (!string.IsNullOrEmpty(areaCode))
            {
                if (areaCode.EndsWith("0000"))
                {
                    sqlBuilder.AppendFormat(" and AREA_CODE like'{0}%'", areaCode.TrimEnd('0').PadRight(2, '0'));
                }
                else if (areaCode.EndsWith("00"))
                {
                    sqlBuilder.AppendFormat(" and AREA_CODE like'{0}%'", areaCode.TrimEnd('0').PadRight(4, '0'));
                }
                else
                {
                    sqlBuilder.AppendFormat(" and AREA_CODE='{0}'", areaCode);
                }
            }
            if (!string.IsNullOrEmpty(shopCode))
            {
                sqlBuilder.AppendFormat(" and shop_code='{0}'", shopCode);
            }
            else if (!string.IsNullOrEmpty(shopName))
            {
                sqlBuilder.AppendFormat(" and shop_code in (SELECT SHOP_CODE FROM SHOP WHERE LIKE '%{0}%')", shopName);
            }
            if (noMobile) sqlBuilder.AppendFormat(" and ([mobile] is not null and mobile<>'')");
            if (!string.IsNullOrEmpty(mobileNum)) sqlBuilder.AppendFormat(" and mobile like '%{0}%'", mobileNum);
            return DB.HR.ExecuteDataSet(CommandType.Text, sqlBuilder.ToString()).Tables[0];
        }

        /// <summary>
        /// 获得提醒数据
        /// </summary>
        /// <param name="dateValue">多少天</param>
        /// <param name="remindType">0:customer1:lvxing2:swing;3:耗材</param>
        /// <param name="currentUser"></param>
        /// <param name="pageCurrent"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageCount"></param>
        /// <param name="rowCount"></param>
        /// <returns></returns>
        public static DataTable GetRemindByRemindTypePaging(int dateValue, int remindType, Guid currentUser, string productName,
            int pageCurrent, int pageSize, ref int pageCount, ref int rowCount)
        {
            var db = DB.HR;
            var command = db.GetStoredProcCommand("proc_sms_reminder_paging");
            db.AddInParameter(command, "@dayValue", DbType.Int32, dateValue);
            db.AddInParameter(command, "@reminderType", DbType.Int32, remindType);
            db.AddInParameter(command, "@mgrUser", DbType.Guid, currentUser);
            db.AddInParameter(command, "@productName", DbType.String, string.Format("%{0}%", productName));
            db.AddInParameter(command, "@pageCurrent", DbType.Int32, pageCurrent);
            db.AddInParameter(command, "@pageSize", DbType.Int32, pageSize);
            db.AddOutParameter(command, "@pageCount", DbType.Int32, pageCount);
            db.AddOutParameter(command, "@recordCount", DbType.Int32, rowCount);
            var rslt = db.ExecuteDataSet(command).Tables[0];
            pageCount = Convert.ToInt32(command.Parameters["@pageCount"].Value.ToString());
            rowCount = Convert.ToInt32(command.Parameters["@recordCount"].Value.ToString());
            return rslt;
        }

        public string AddPhoto(string CUSTOMER_KEY, string PHOTO_URL)
        {
            var db = DB.HR;
            DbCommand dbCommand = db.GetStoredProcCommand("proc_customer_photo_add");
            db.AddInParameter(dbCommand, "@CUSTOMER_KEY", DbType.String, CUSTOMER_KEY);
            db.AddInParameter(dbCommand, "@PHOTO_URL", DbType.String, PHOTO_URL);
            DataTable dt = db.ExecuteDataSet(dbCommand).Tables[0];
            if (dt.Rows.Count > 0)
                return dt.Rows[0][0].ToString();
            else
                return "0";
        }

        public string GetPhotoUrlByKey(string customer_key)
        {
            var strSql = string.Format(" SELECT PHOTO_URL FROM CUSTOMER_PHOTO WHERE CUSTOMER_KEY='{0}'", customer_key);
            var dbClass = DB.HR;
            var cmd = dbClass.GetSqlStringCommand(strSql);
            var dt = dbClass.ExecuteDataSet(cmd).Tables[0];

            if (dt.Rows.Count > 0)
                return dt.Rows[0]["PHOTO_URL"].ToString();
            else
                return "";
        }
    }
}
